﻿/**
 * 数据库元数据类，采用单例设计模式，为其他类提供数据库元数据信息
 * @author dengzongrong
 * @version 1.1
 * @date 2011-10-09
 */
package com.lemon.ai.socket.packets.spiritgame.model.db;

import java.io.File;
import java.io.FileInputStream;
import java.sql.Connection;
import java.sql.DatabaseMetaData;
import java.sql.DriverManager;
import java.sql.ResultSet;
import java.sql.ResultSetMetaData;
import java.sql.Statement;
import java.util.ArrayList;
import java.util.List;
import java.util.Properties;

import com.lemon.ai.socket.packets.spiritgame.model.dto.Column;
import com.lemon.ai.socket.packets.spiritgame.model.dto.Table;

public final class MetaData {

	private static MetaData instance = null;
	private static Connection conn;
	private static String driver;
	private static String url;
	private static String user;
	private static String pass;

	Properties prop = new Properties();
	/**
	 * 私有构造方法，保证这个类生成的对象只有一个
	 */
	private MetaData() {
		try {

			prop.load(new FileInputStream(new File(
					System.getProperty("user.dir") + "\\mysql.properties")));
			driver = prop.getProperty("driver");
			url = prop.getProperty("url");
			user = prop.getProperty("username");
			pass = prop.getProperty("password");
			Class.forName(driver);
			conn = DriverManager.getConnection(url, user, pass);
		} catch (Exception e) {
			e.printStackTrace();
		}
	}

	/**
	 * 获得这个类唯一的对象
	 * @return instance
	 */
	public static MetaData getInstance() {
		if (instance == null) {
			synchronized (MetaData.class) {
				if (instance == null) {
					instance = new MetaData();
				}
			}
		}
		return instance;
	}

	/**
	 * 获取该数据库的所有表名
	 * 
	 * @return List
	 *         <Table>
	 */
	public List<Table> getTables() {
		List<Table> tables = null;
		try {
			tables = new ArrayList<Table>();
			DatabaseMetaData meta = conn.getMetaData();
			ResultSet trs = meta.getTables(null, null, null,
					new String[] { "TABLE" });
			while (trs.next()) {
				Table table = new Table();
				//设置表名
				table.setName(trs.getString(3));
				//设置表主键的集合
				ResultSet pkrs = meta.getPrimaryKeys(null, null, trs
						.getString(3));
				while (pkrs.next()) {
					Column label = this.getColumnByColumnName(pkrs.getString(4),
							trs.getString(3));
					//					table.getKeys().add(label);
					table.setKey(label);
				}
				//设置表所有列的集合
				Statement stat = conn.createStatement();
				ResultSet rs = stat.executeQuery("select * from " + trs.getString(3));
				ResultSetMetaData columnsMeta = rs.getMetaData();
				for (int i = 1; i <= columnsMeta.getColumnCount(); i++) {
					Column label = new Column();
					label.setName(columnsMeta.getColumnLabel(i));
					label.setType(columnsMeta.getColumnType(i));
					label.setTypename(columnsMeta.getColumnTypeName(i));
					table.getColumns().add(label);
				}

				if( prop.getProperty("tablename").isEmpty() ){
					tables.add(table);
				} else {
					for (String tableConfig : prop.getProperty("tablename").split(",")) {
						String tableName = tableConfig.replaceAll(" findby.*", "");
						String [] findByFields = 
								tableConfig.replaceAll(tableName, "").
								replaceAll("findby", "").
								replaceAll(" ", "").split("/")
								;
						if(tableName.equals( table.getName())) {
							table.addFindByMethod(findByFields);
							tables.add( table );
						}
					}
				}
			}
		} catch (Exception e) {
			e.printStackTrace();
		}
		return tables;
	}

	/**
	 * 根据表明获取该表的所有字段名称和字段类型
	 * 
	 * @param tableName
	 *            表名
	 * @return 由表明和类型组成的LabelDTO的List
	 */
	public List<Column> getColumnsByTableName(String tableName) {
		List<Column> labels = null;
		try {
			labels = new ArrayList<Column>();
			Statement stat = conn.createStatement();
			ResultSet rs = stat.executeQuery("select * from " + tableName);
			ResultSetMetaData meta = rs.getMetaData();
			for (int i = 1; i <= meta.getColumnCount(); i++) {
				Column label = new Column();
				label.setName(meta.getColumnLabel(i));
				label.setType(meta.getColumnType(i));
				label.setTypename(meta.getColumnTypeName(i));
				labels.add(label);
			}
		} catch (Exception e) {
			e.printStackTrace();
		}
		return labels;
	}

	/**
	 * 根据列名和表名获取该列的详细信息
	 * 
	 * @param columnName
	 *            列名
	 * @param tableName
	 *            表名
	 * @return LabelDTO
	 */
	public Column getColumnByColumnName(String columnName, String tableName) {
		Column label = null;
		try {
			label = new Column();
			Statement stat = conn.createStatement();
			ResultSet rs = stat.executeQuery("select " + columnName + " from "
					+ tableName);
			ResultSetMetaData meta = rs.getMetaData();
			label.setName(meta.getColumnLabel(1));
			label.setType(meta.getColumnType(1));
			label.setTypename(meta.getColumnTypeName(1));
		} catch (Exception e) {
			e.printStackTrace();
		}
		return label;
	}

	public static void main(String[] args) {
		MetaData meta = MetaData.getInstance();
		System.out.println(meta.getTables().size());
	}

}
